Java 多线程导入excel数据

发表于 | 分类于
字数统计: 5,886 字 | 阅读时长 ≈ 30 分钟

Java 多线程导入excel数据

项目源码：

  • 前端springboo-excel
  • 后端springboot-excel-process

‍

本质上是如何高效地处理大量的数据集合：

  • 从一个数据库抓取另一个数据库的大量数据
  • 从大文件Excel中导入到数据库

目标分解

使用springboot，方便使用依赖和后续的文件上传

  1. 使用POI生成Excel文件

  2. 使用POI解析Excel文件

  3. 多线程拆分Excel文件，并导入到数据库，观察耗时

    1. 尝试使用多线程以降低耗时
    2. 使用 spring jdbc template，前端使用MultipartFile
    3. 生产者读取，消费者写入

‍

Springboot通用配置

0.application.yml

1
2
3
spring:
  profiles:
    active: oracle

‍

1.application-oracle.yml，用于连接oracle数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
server:
  port: 8003

spring:
  # 数据源的相关配置
  datasource:
    type: com.zaxxer.hikari.HikariDataSource          # 数据源类型：HikariCP
    driver-class-name: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@10.38.1.101:1521:PROD
    username: apps
    password: It2017
    hikari:
      connection-timeout: 30000       # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 默认:30秒
      minimum-idle: 5                 # 最小连接数
      maximum-pool-size: 20           # 最大连接数
      auto-commit: true               # 自动提交
      idle-timeout: 600000            # 连接超时的最大时长（毫秒），超时则被释放（retired），默认:10分钟
      pool-name: DateSourceHikariCP   # 连接池名字
      max-lifetime: 1800000           # 连接的生命时长（毫秒），超时而且没被使用则被释放（retired），默认:30分钟 1800000ms
      connection-test-query: SELECT 1 FROM DUAL
  application:
    name: spring-excel

‍

2.pom.xml 使用jdk1.8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>springboot-excel-process</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.3.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.4.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.4.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.6</version>
        </dependency>

        <!-- apache poi -->
        <!-- 用于操作微软办公文档 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

        <!-- Spring Jdbc Template -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.3.5.RELEASE</version>
        </dependency>

        <!-- Oralce Jdbc -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>12.2.0.1</version>
        </dependency>

        <!-- fastJson -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
            <version>2.10.2</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.10.2</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.10.2</version>
        </dependency>

    </dependencies>

    <build>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <!-- Java 编译 -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

‍

3.swagger2.java接口调试配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.excel.config;

import com.google.common.base.Predicate;
import com.google.common.base.Predicates;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.RequestHandler;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@EnableSwagger2
public class Swagger2 {

//    http://localhost:8088/swagger-ui.html     原路径
//    http://localhost:8088/doc.html     新路径，github开源的swagger页面，自带汉化
    // 配置swagger2核心配置 docket
    @Bean
    public Docket createRestApi() {

        Predicate<RequestHandler> excelPredicate = RequestHandlerSelectors.basePackage("com.excel.controller");

        return new Docket(DocumentationType.SWAGGER_2)  // 指定api类型为swagger2
                .apiInfo(apiInfo())                 // 用于定义api文档汇总信息
                .select()
                .apis(Predicates.or(excelPredicate))
                .paths(PathSelectors.any())         // 所有controller
                .build();
    }
    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("sora blog·博客接口api")                       // 文档页标题
                .contact(new Contact("sora",
                        "https://www.kalosora.work",
                        "kalosora@gmail.com"))                   // 联系人信息
                .description("sora blog博客的api文档")      // 详细信息
                .version("1.0.0")                               // 文档版本号
                .termsOfServiceUrl("https://www.kalosora.work")     // 网站地址
                .build();
    }
}

‍

‍

生成Excel文件

生成简单的Excel表格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
@RestController
@RequestMapping("excel")
@Api(value = "EXCEL处理相关Controller", tags = {"EXCEL处理相关Controller"})
public class ExcelController {

    /**
     * 创建Excel文件
     * 一个Excel文件，包括多个Sheet；一个Sheet包括多个Cell
     * 一个Cell包括多个Column
     *
     * @return
     */
    @GetMapping("createSampleExcel")
    @ApiOperation(value = "创建简单Excel文件", notes = "创建简单Excel文件", httpMethod = "GET")
    public String createExcel() throws IOException {

        String excelFilePath = "/Users/kalosora/Desktop/sample.xls";
        File excelFile = new File(excelFilePath);
        if (excelFile.exists()) {
            excelFile.createNewFile();
        }

        // 开始创建Excel
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        // 从第0行开始创建
        HSSFRow row = sheet.createRow(0);
        // 设置单元格内容，从0开始
        row.createCell(0).setCellValue("发票编号");
        row.createCell(1).setCellValue("经营单位");
        row.createCell(2).setCellValue("GL日期");

        // 设置Excel备注信息
        setExcelDesc(workbook);

        // 写入到Excel
        FileOutputStream fos = new FileOutputStream(excelFile);
        workbook.write(fos);
        workbook.close();

        return "创建成功!输出到:" + excelFilePath;
    }

    /**
     * 设置Excel文档信息
     * @param workbook
     */
    private void setExcelDesc(HSSFWorkbook workbook) {
        workbook.createInformationProperties();                     //创建文档信息
        DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();//摘要信息
        dsi.setCategory("Excel文件");                                //类别
        dsi.setManager("kalosora");                                 //管理者
        dsi.setCompany("--");                                       //公司
        SummaryInformation si = workbook.getSummaryInformation();   //摘要信息
        si.setSubject("测试文档");                                   //主题
        si.setTitle("测试文档");                                     //标题
        si.setAuthor("kalosora");                                   //作者
        si.setComments("POI测试文档");                               //备注
    }
}

‍

改进：从数据库导出到excel

使用spring jdbc Template导出excel文件

Repository层，ApInvoiceVO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.excel.repository;

/**
 * 发票实体类VO
 */
public class ApInvoiceVO {

    // 发票编号
    private String invoiceNum;

    // 经营单位
    private String orgName;

    // 总账日期
    private String glDate;

    // getter & setter
    public String getInvoiceNum() {
        return invoiceNum;
    }

    public void setInvoiceNum(String invoiceNum) {
        this.invoiceNum = invoiceNum;
    }

    public String getOrgName() {
        return orgName;
    }

    public void setOrgName(String orgName) {
        this.orgName = orgName;
    }

    public String getGlDate() {
        return glDate;
    }

    public void setGlDate(String glDate) {
        this.glDate = glDate;
    }
}

‍

Service层，ApInvoiceService.java，使用Jdbc Template

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
 * 发票Service层
 */
@Service
public class ApInvoiceService {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ApInvoiceService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 查询发票信息
     *
     * @return
     */
    public List<ApInvoiceVO> queryInvoiceInfo(String querySql) {
        return jdbcTemplate.query(querySql, new Object[]{}, new BeanPropertyRowMapper<>(ApInvoiceVO.class));
    }
}

‍

Controller层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
@GetMapping("/createExcelFromDB")
@ApiOperation(value = "从数据库导出Excel文件", notes = "从数据库导出Excel文件", httpMethod = "GET")
public String createExcelFromDB() throws IOException {

    // 获取数据源
    StringBuilder sb = new StringBuilder();
    sb.append("select aia.invoice_num,hou.name org_name,to_char(aia.gl_date,'yyyy-mm-dd hh24:mi:ss') gl_date ");
    sb.append("from ap_invoices_all aia,hr_operating_units hou ");
    sb.append("where 1=1 and aia.org_id = hou.organization_id ");
    sb.append("and gl_date >= to_date('2021-05-01','yyyy-mm-dd')");

    // fixme：测试sql语句
    System.out.println("查询语句:" + sb.toString());

    List<ApInvoiceVO> invoiceVOList = apInvoiceService.queryInvoiceInfo(sb.toString());

    String excelFilePath = "/Users/kalosora/Desktop/sampleDB.xls";
    File excelFile = new File(excelFilePath);
    if (excelFile.exists()) {
        excelFile.createNewFile();
    }

    // 开始创建Excel
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sheet1");
    // 设置表头
    HSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue("发票编号");
    row.createCell(1).setCellValue("经营单位");
    row.createCell(2).setCellValue("GL日期");
    // 写入到Sheet1中
    for (int i = 1; i < invoiceVOList.size(); i++) {
        row = sheet.createRow(i);
        // 设置单元格内容，从0开始
        row.createCell(0).setCellValue(invoiceVOList.get(i).getInvoiceNum());
        row.createCell(1).setCellValue(invoiceVOList.get(i).getOrgName());
        row.createCell(2).setCellValue(invoiceVOList.get(i).getGlDate());
    }

    // 设置Excel备注信息
    setExcelDesc(workbook);

    // 写入到Excel
    FileOutputStream fos = new FileOutputStream(excelFile);
    workbook.write(fos);
    workbook.close();

    return "创建成功!输出到:" + excelFilePath;
}

‍

改进：freemarker导出到excel

上传sql文件，并且导出到excel

步骤分解

  1. 从file中获取sql语句
  2. 读取select后，from前的所有字段，生成字节码（动态生成实体类）
  3. 连接数据库，通过freemarker导出到excel

‍

从Excel导入

解析简单的Excel表格

excel包含少量数据，使用Spring Jdbc Template处理，并在数据库创建记录

【注意】xls格式最大行数为65535，以二进制形式存储文件；xlsx格式以xml形式存储文件，可以最多存储100w+行数据

前端：excel.html，使用Vue

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<!DOCTYPE html>

<head>
    <meta charset="UTF-8" />
    <title>Excel文件上传页面</title>
</head>

<body>
    <div id="excelProcess">
        请选择excel文件: <input type="file" ref="file" /><br>
        <input type="button" value="提交" @click="excelUpload" /><br>
        <input type="button" value="测试连接" @click="testPostStatus" />
    </div>
</body>

<script src="./vue/axios.min.js"></script>
<script src="./vue/vue.min.js"></script>
<script type="text/javascript">
    var excelProcess = new Vue({
        el: "#excelProcess",
        data: {

        },
        mounted() {

        },
        created() {

        },
        methods: {

            // 测试前后端连接
            testPostStatus() {
                var postUrl = "http://localhost:8003/testPost?postMsg=";
                var msg = "从VUE发送消息!";
                axios.post(postUrl + msg).then(res => {
                    console.log(res.data);

                    if (res.data.status == 200) {
                        alert("连接服务器成功");
                    }
                    else {
                        alert("连接服务器失败");
                    }
                });
            },

            // 上传Excel文件
            excelUpload() {
                var excelUploadUrl = "http://localhost:8003/excel/readExcelIntoDB";
                var formData = new FormData();

                formData.append('excel', this.$refs.file.files[0]);

                axios.post(excelUploadUrl, formData).then(res => {
                    console.log(res.data);

                    if (res.data.status == 200) {
                        alert("文件上传成功！");
                    }
                    else {
                        alert("文件解析失败！" + res.data.msg);
                    }
                });
            },
        }

    });
</script>

</html>

‍

后端，连接的是mysql数据库

ExcelService.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.excel.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class ExcelService {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ExcelService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void insertExcelData(String sqlStr)
    {
        jdbcTemplate.update(sqlStr);
    }
}

‍

ExcelController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
/**
 * 从数据库导出到Excel
 *
 * @return
 * @throws IOException
 */
@GetMapping("/createExcelFromDB")
@ApiOperation(value = "从数据库导出Excel文件", notes = "从数据库导出Excel文件", httpMethod = "GET")
public String createExcelFromDB() throws IOException {

    // 获取数据源
    StringBuilder sb = new StringBuilder();
    sb.append("select aia.invoice_num,hou.name org_name,to_char(aia.gl_date,'yyyy-mm-dd hh24:mi:ss') gl_date ");
    sb.append("from ap_invoices_all aia,hr_operating_units hou ");
    sb.append("where 1=1 and aia.org_id = hou.organization_id ");
    sb.append("and gl_date >= to_date('2021-05-01','yyyy-mm-dd')");

    // fixme：测试sql语句
    System.out.println("查询语句:" + sb.toString());

    List<ApInvoiceVO> invoiceVOList = apInvoiceService.queryInvoiceInfo(sb.toString());

    String excelFilePath = "/Users/kalosora/Desktop/sampleDB.xls";
    File excelFile = new File(excelFilePath);
    if (excelFile.exists()) {
        excelFile.createNewFile();
    }

    // 开始创建Excel
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sheet1");
    // 设置表头
    HSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue("发票编号");
    row.createCell(1).setCellValue("经营单位");
    row.createCell(2).setCellValue("GL日期");
    // 写入到Sheet1中
    for (int i = 1; i < invoiceVOList.size(); i++) {
        row = sheet.createRow(i);
        // 设置单元格内容，从0开始
        row.createCell(0).setCellValue(invoiceVOList.get(i).getInvoiceNum());
        row.createCell(1).setCellValue(invoiceVOList.get(i).getOrgName());
        row.createCell(2).setCellValue(invoiceVOList.get(i).getGlDate());
    }

    // 设置Excel备注信息
    setExcelDesc(workbook);

    // 写入到Excel
    FileOutputStream fos = new FileOutputStream(excelFile);
    workbook.write(fos);
    workbook.close();

    return "创建成功!输出到:" + excelFilePath;
}

/**
 * 解析Excel文件，并插入到数据库
 *
 * @return
 */
@PostMapping("/readExcelIntoDB")
@ApiOperation(value = "解析Excel文件并插入到数据库", notes = "解析Excel文件并插入到数据库", httpMethod = "POST")
public Map<String, Object> readExcelIntoDB(@RequestParam("excel") MultipartFile excel) {
    Map<String, Object> resultMap = new HashMap<>();

    // 校验文件类型
    String originFile = excel.getOriginalFilename();
    if (!originFile.contains("xls") && !originFile.contains("xlsx")) {
        resultMap.put("status", -1);
        resultMap.put("msg", "无法解析的Excel文件类型");
        return resultMap;
    }

    // 解析Excel文件，xls文件最大行数65535，使用POI进行简单的数据导入
    Map<Integer, List<String>> excelMap = new HashMap<>();
    try {
        InputStream is = excel.getInputStream();
        Workbook workBook = new HSSFWorkbook(is, true);
        excelMap = processWorkBook(workBook);

    } catch (IOException e) {
        System.out.println("获取Excel文件失败：" + e.getMessage());
    }

    // 拼接Mysql SQL语句,当然也可以采用实体类的方式。拼接的方式比较节省空间
    StringBuilder sb = new StringBuilder();
    sb.append("insert into `sample-table` (column1,column2,column3) values ");
    // 插入到数据库，第0行一般为标题，故忽略
    for (int i = 1; i < excelMap.size(); i++) {
        List<String> columnList = excelMap.get(i);

        StringJoiner joiner = new StringJoiner(",", "(", "),");
        for (String column : columnList) {
            column = "'" + column + "'";
            joiner.add(column);
        }
        sb.append(joiner);
    }
    String sqlStr = sb.toString();
    sqlStr = sqlStr.substring(0, sqlStr.lastIndexOf(","));
    System.out.println("sql语句:" + sqlStr);

    // 插入到数据库
    excelService.insertExcelData(sqlStr);

    resultMap.put("status", 200);
    return resultMap;
}

/**
 * 处理Excel文件
 *
 * @param workbook
 * @return
 */
private Map<Integer, List<String>> processWorkBook(Workbook workbook) {
    Sheet sheet = workbook.getSheetAt(0);

    // 二维集合对应Excel二维表
    Map<Integer, List<String>> map = new HashMap<>();

    int i = 0;
    for (Row row : sheet) {
        map.put(i, new ArrayList<String>());
        for (Cell cell : row) { // 遍历当前行的所有cell
            switch (cell.getCellType()) {
                case STRING:
                    map.get(i).add(cell.getRichStringCellValue().getString()); // 如果是字符串则保存
                    break;
                case _NONE:
                    break;
                case NUMERIC:
                    map.get(i).add(cell.getNumericCellValue() + ""); //将数值转换为字符串
                    break;
                case BOOLEAN:
                    break;
                case FORMULA:
                    break;
                case BLANK:
                    break;
                case ERROR:
                    break;
            }
        }
        i++;
    }

    return map;
}

‍

‍

改进：添加AOP并观察单线程运行速度

尝试导入10w条数据，并观察导入的速度，添加AOP拦截

AOP配置

0.修改application.yml配置文件，修改允许上传文件的尺寸

1
2
3
4
5
6
7
spring:
  profiles:
    active: mariadb
  servlet:
    multipart:
      max-file-size: 50MB
      max-request-size: 50MB

1.pom.xml引入AOP依赖

1
2
3
4
5
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-aop</artifactId>
  <version>2.2.5.RELEASE</version>
</dependency>

2.Aspect配置类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
package com.excel.config;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class ServiceLogAspect {

    final static Logger logger = LoggerFactory.getLogger(ServiceLogAspect.class);

    /**
     * AOP通知：
     * 1.前置通知
     * 2.后置通知
     * 3.环绕通知：调用service之前和之后执行
     * 4.异常通知：service发生异常的时候通知
     * 5.最终通知
     */

    @Around("execution(* com.excel.controller.*.*(..))")
    public Object recordTimeOfService(ProceedingJoinPoint joinPoint) throws Throwable {
        logger.info("==== 开始执行 {}.{} ====",
                joinPoint.getTarget().getClass(),
                joinPoint.getSignature().getName());

        long start = System.currentTimeMillis();

        Object result = joinPoint.proceed();

        long end = System.currentTimeMillis();
        long takeTime = end - start;

        if (takeTime > 3000) {
            logger.error("当前执行耗时：{}", takeTime);
        } else if (takeTime > 2000) {
            logger.warn("当前执行耗时：{}", takeTime);
        } else {
            logger.info("当前执行耗时：{}", takeTime);
        }

        return result;
    }
}

‍

数据导入测试

准备了10w行的xls文档，导入到空表，观察测试结果

![image.png](Java 多线程导入excel数据/image-20210526102050-srjdrwj.png)

‍

实际测试发现，仅能导入65535行，耗时1875ms

springboot单机导入

![image.png](Java 多线程导入excel数据/image-20210526103408-jsigw9k.png)

mysql数据总数

![image.png](Java 多线程导入excel数据/image-20210526102956-x5hphwk.png)

‍

解析xlsx的Excel表格

pom.xml添加依赖

1
2
3
4
5
6
<!-- 用于操作微软办公文档2007 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

添加XLSX解析的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
@PostMapping("/readExcelXLSXIntoDB")
@ApiOperation(value = "解析xlsx文件并插入到数据库", notes = "解析xlsx文件并插入到数据库", httpMethod = "POST")
public Map<String, Object> readExcelXLSXIntoDB(@RequestParam("excel") MultipartFile excel) {
    Map<String, Object> resultMap = new HashMap<>();

    // 校验文件类型
    String originFile = excel.getOriginalFilename();
    if (!originFile.contains("xls") && !originFile.contains("xlsx")) {
        resultMap.put("status", -1);
        resultMap.put("msg", "无法解析的Excel文件类型");
        return resultMap;
    }

    // 解析Excel文件，xlsx文件最大行数100w+
    Map<Integer, List<String>> excelMap = new HashMap<>();
    try {
        InputStream is = excel.getInputStream();
        Workbook workBook = new XSSFWorkbook(is);
        excelMap = processWorkBook(workBook);

    } catch (IOException e) {
        System.out.println("获取Excel文件失败：" + e.getMessage());
    }

    // 拼接Mysql SQL语句,当然也可以采用实体类的方式。拼接的方式比较节省空间
    StringBuilder sb = new StringBuilder();
    sb.append("insert into `sample-table` (column1,column2,column3) values ");
    // 插入到数据库，第0行一般为标题，故忽略
    for (int i = 1; i < excelMap.size(); i++) {
        List<String> columnList = excelMap.get(i);

        StringJoiner joiner = new StringJoiner(",", "(", "),");
        for (String column : columnList) {
            column = "'" + column + "'";
            joiner.add(column);
        }
        sb.append(joiner);
    }
    String sqlStr = sb.toString();
    sqlStr = sqlStr.substring(0, sqlStr.lastIndexOf(","));
    System.out.println("sql语句:" + sqlStr);

    // 插入到数据库
    excelService.insertExcelData(sqlStr);

    resultMap.put("status", 200);
    return resultMap;
}

‍

实际测试发现，导入10W行

![image.png](Java 多线程导入excel数据/image-20210526160723-yzwn6bj.png)

‍

mysql数据总数

![image.png](Java 多线程导入excel数据/image-20210526160746-jrxa084.png)

‍

多线程导入

场景：Springboot 单线程导入100w行Excel数据，报GC溢出

GC Overhead Limit Exceeded Error简介

OutOfMemoryError是java.lang.VirtualMachineError的子类，当JVM资源利用出现问题时抛出，更具体地说，这个错误是由于JVM花费太长时间执行GC且只能回收很少的堆内存时抛出的。根据Oracle官方文档，默认情况下，如果Java进程花费98%以上的时间执行GC，并且每次只有不到2%的堆被恢复，则JVM抛出此错误。换句话说，这意味着我们的应用程序几乎耗尽了所有可用内存，垃圾收集器花了太长时间试图清理它，并多次失败。

在这种情况下，用户会体验到应用程序响应非常缓慢，通常只需要几毫秒就能完成的某些操作，此时则需要更长的时间来完成，这是因为所有的CPU正在进行垃圾收集，因此无法执行其他任务。

![image.png](Java 多线程导入excel数据/image-20210527134344-07tmkad.png)

‍

改进：生产者消费者模型

步骤分解

  1. 获取上传的文件并保存副本到临时目录

  2. 确定长度

    1. 文件总长度
    2. 每个线程处理的任务长度
    3. 生产者预计线程数，根据机器性能给定，或者用 文件长度/线程任务长度
    4. 消费者预计线程数，根据机器性能给定，或者按比例分配（生产者:消费者 = 4:1）

  3. 数据分片处理

    1. 多线程任务一般都是需要进行数据分片

    2. 任务开始位置：I * 线程任务长度

    3. 任务结束位置：（I+1） * 线程任务长度

    4. Excel文件处理

      1. sheet.getRow(startPos).getRowNum()获取特定行数进行行循环
      2. Map<Integer, List<String>>定义数据结构保存Excel二维表
      3. map.get(i).add(cell.getRichStringCellValue().getString())进行列循环并保存到数据结构中
      4. 拼接成mysql的insert into语句，并放入生产者队列中

  4. 开启多线程

    1. ExecutorService分别开启生产者和消费者线程
    2. CountDownLatch观察生产者任务（转换mysql语句）是否完成
    3. LinkedBlockingQueue线程安全的无界链队列，保存mysql语句供消费者调用

‍

实例代码：

Controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
@PostMapping("/readXLSXMultiIntoDB")
@ApiOperation(value = "多线程导入xlsx文件到数据库", notes = "多线程导入xlsx文件到数据库", httpMethod = "POST")
public Map<String, Object> readXLSXMultiIntoDB(@RequestParam("excel") MultipartFile excel) {
    Map<String, Object> resultMap = new HashMap<>();

    // TODO 多线程处理EXCEL时，文件要先保存到本地
    // https://blog.csdn.net/bieber007/article/details/109963775
    MultipartFile multipartFile = excel;
    String localStorePath = "/Users/kalosora/Desktop/exceltmp/" + excel.getOriginalFilename();
    try {
        multipartFile.transferTo(new File(localStorePath));
    } catch (IOException e) {
        System.out.println("保存上传文件失败:" + e.getMessage());
    }

    // 打开本地文件
    File localFile = new File(localStorePath);
    Workbook workBook = null;
    Sheet sheet = null;
    try {
        InputStream is = new FileInputStream(localFile);
        workBook = new XSSFWorkbook(is);
        // 默认读取第一个表格
        sheet = workBook.getSheetAt(0);

    } catch (IOException e) {
        resultMap.put("status", -1);
        resultMap.put("msg", "解析Excel文件失败: " + e.getMessage());
    }

    // 模拟已知文档长度，如果计算的话需要50秒，这里节省性能
    Integer fileLength = 1000000;  //500000，单个线程处理5000，通过测试，耗时335s

    // 假设每个线程处理5000数据
    Integer threadLength = 5000;

    // 计算生产者 & 消费者线程数
    final Integer producerThreadCount = fileLength / threadLength;
    final Integer consumerThreadCount = producerThreadCount / 4; // 生产者:消费者 4:1

    // 任务队列
    LinkedBlockingQueue<String> excelQueue = new LinkedBlockingQueue<>();
    CountDownLatch latch = new CountDownLatch(producerThreadCount);

    // 机器性能限制，只开启100个线程
    ExecutorService executorService = Executors.newFixedThreadPool(100);
    ExecutorService consumerService = Executors.newFixedThreadPool(consumerThreadCount);

    // 启动生产者
    for (int i = 0; i < producerThreadCount; i++) {
        // 创建线程副本
        Integer LocalI = i;
        Sheet localSheet = sheet;

        executorService.submit(() -> {
            Integer startPos = LocalI * threadLength + 1;
            Integer endPos = (LocalI + 1) * threadLength;
            CountDownLatch localCountDown = latch;

            processXLSXMulti(excelQueue, localCountDown, localSheet, startPos, endPos);
        });
    }

    // 启动消费者
    for (int i = 0; i < consumerThreadCount; i++) {
        consumerService.submit(() -> {
            while (true) {
                if (excelQueue.size() > 0) {
                    String sqlStr = excelQueue.poll();
                    // 插入到数据库
                    excelService.insertExcelData(sqlStr);
                }
            }
        });
    }

    try {
        latch.await();
        System.out.println("生产者任务已完成!");
    } catch (InterruptedException e) {
        System.out.println("任务屏障失败:" + e.getMessage());
    }

    // 阻塞，等待消费者任务执行完毕
    System.out.println("等待消费者任务 ...");
    while (true) {
        if (excelQueue.size() <= 0) {
            executorService.shutdown();
            break;
        }
        else
        {
            try {
                Thread.sleep(3000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }

    resultMap.put("status", 200);
    return resultMap;
}

/**
 * 生产者：读取XLSX数据
 *
 * @param sheet
 * @param startPos Excel开始位置
 * @param endPos   Excel结束为止
 * @return
 */
private void processXLSXMulti(LinkedBlockingQueue<String> excelQueue,
                              CountDownLatch localCountDown,
                              Sheet sheet,
                              Integer startPos,
                              Integer endPos) {

    // 二维集合对应Excel二维表
    Map<Integer, List<String>> excelMap = new HashMap<>();

    // 获取起止行数
    Integer startRowNum = sheet.getRow(startPos).getRowNum();
    Integer endRowNum = sheet.getRow(endPos).getRowNum();

    // 行循环
    int count = 0;
    for (int row = startRowNum; row <= endRowNum; row++) {
        Row currentRow = sheet.getRow(row);
        excelMap.put(count, new ArrayList<String>());

        // 列循环
        for (Cell cell : currentRow) { // 遍历当前行的所有cell
            switch (cell.getCellType()) {
                case STRING:
                    excelMap.get(count).add(cell.getRichStringCellValue().getString()); // 如果是字符串则保存
                    break;
                case _NONE:
                    break;
                case NUMERIC:
                    excelMap.get(count).add(cell.getNumericCellValue() + ""); //将数值转换为字符串
                    break;
                case BOOLEAN:
                    break;
                case FORMULA:
                    break;
                case BLANK:
                    break;
                case ERROR:
                    break;
            }
        }

        count++;
    }

    System.out.println("[线程" + Thread.currentThread().getId() + "] "
            + "EXCEL开始行数: " + startRowNum
            + "，EXCEL结束行数: " + endRowNum
            + ", 列表长度：" + excelMap.size());

    // 拼接Mysql SQL语句,当然也可以采用实体类的方式。拼接的方式比较节省空间
    StringBuilder sb = new StringBuilder();
    sb.append("insert into `sample-table` (column1,column2,column3) values ");
    // 插入到数据库，第0行一般为标题，故忽略
    for (int k = 0; k < count; k++) {
        List<String> columnList = excelMap.get(k);

        StringJoiner joiner = new StringJoiner(",", "(", "),");
        for (String column : columnList) {
            column = "'" + column + "'";
            joiner.add(column);
        }
        sb.append(joiner);
    }
    String sqlStr = sb.toString();
    sqlStr = sqlStr.substring(0, sqlStr.lastIndexOf(","));

    // 任务入队
    excelQueue.offer(sqlStr);
    localCountDown.countDown();

    System.out.println("[线程" + Thread.currentThread().getId() + "] 已完成");
}

‍

Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Service
public class ExcelService {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ExcelService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void insertExcelData(String sqlStr)
    {
        jdbcTemplate.update(sqlStr);
    }
}

‍

进一步优化的方案

提升Excel导入速度的方法：

  • 使用更快的 Excel 读取框架(推荐使用阿里 EasyExcel)
  • 对于需要与数据库交互的校验、按照业务逻辑适当的使用缓存。用空间换时间
  • 使用 values(),(),() 拼接长 SQL 一次插入多行数据
  • 使用多线程插入数据，利用掉网络IO等待时间(推荐使用并行流，简单易用)
  • 避免在循环中打印无用的日志

https://zhuanlan.zhihu.com/p/258525453

‍

导出到Excel

从plsql中导出100w条数据，耗时745秒

多线程目标：导出100w条数据，至少优化到400秒以下

![image.png](Java 多线程导入excel数据/image-20210527133017-h2hde4n.png)

有空再做吧…

‍

‍

参考来源

java多线程进行大批量EXcel数据导入实现方案

java POI详解

java POI 实例

代替POI的EasyExcel

Spring Jdbc Template

‍

0%